Option Explicit

Sub BookInSQL()

Dim dic_bk As Object, cnnPP As Object, rst As Object, cnnStr$, wb As Workbook, bk, sht As Worksheet, wbPP As Workbook, shtPP As Worksheet, sql$, i%, shtPPName$

'Application.ScreenUpdating = False

Set cnnPP = CreateObject("ADODB.Connection"): Set rst = CreateObject("ADODB.RecordSet"):

cnnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes;IMEX=2';Data Source="

Set dic_bk = 模块2.selectBooks

If Not dic_bk Is Nothing Then

Set wb = ThisWorkbook

For Each bk In dic_bk.keys

Set wbPP = Workbooks.Open(bk): Set shtPP = wbPP.Sheets(1): shtPPName = shtPP.Name

If shtPP.Range("A1").MergeCells Then Call 模块2.cancelCellMerge(shtPP) '取消字段行合并

wbPP.Close SaveChanges:=False '需安装2010-AccessDatabaseEngine_X86.exe

sql = "Select * From [" & shtPPName & "$] Where 产品名 Is Not NULL"

sql = "Select Replace(产品名,'辣条','小辣条'),* From (" & sql & ")"

cnnPP.Open cnnStr & bk: rst.Open sql, cnnPP, 1, 3: sql = ""

If rst.RecordCount Then

With sht

.UsedRange.ClearContents

For i = 0 To rst.Fields.Count - 1

.Cells(1, i + 1).Value = rst.Fields(i).Name

Next: .Range("A2").CopyFromRecordset rst:

.UsedRange.Value = .UsedRange.Value '取消文本型数值

End With

End If: rst.Close: cnnPP.Close

Next

End If

'Application.ScreenUpdating = True

End Sub